﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Threading;
using System.Threading.Tasks;
using YGBookmark.Models;
using YGBookmark.Utils;

namespace YGBookmark.DAL
{
    /// <summary>
    /// SQLite数据库操作帮助类（支持事务管理和自动重连）
    /// </summary>
    public sealed class DatabaseHelper : IDisposable
    {
        #region 单例实现
        private static readonly Lazy<DatabaseHelper> _instance =
            new Lazy<DatabaseHelper>(() => new DatabaseHelper());
        public static DatabaseHelper Instance => _instance.Value;
        #endregion

        #region 常量配置
        private const string DatabaseFile = "Bookmark.db";
        private const int DefaultTimeout = 30;
        private const int MaxRetryCount = 3;
        private const int CurrentVersion = 1;
        #endregion

        private SQLiteConnection _connection;
        private SQLiteTransaction _transaction;
        private readonly SemaphoreSlim _semaphore = new SemaphoreSlim(1, 1);

        public string ConnectionString = "";
        /// <summary>
        /// 初始化数据库连接并执行迁移
        /// </summary>
        private DatabaseHelper()
        {
            InitializeDatabase();
            MigrateDatabase();
        }

        #region 数据库初始化
        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        private void InitializeDatabase()
        {
            try
            {
                var dbPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database", DatabaseFile);
                this.ConnectionString = dbPath;
                var connString = $"Data Source={dbPath};Version=3;";

                if (!File.Exists(dbPath))
                {
                    Directory.CreateDirectory(Path.GetDirectoryName(dbPath));
                    SQLiteConnection.CreateFile(dbPath);
                    Logger.LogInformation("数据库文件已创建");
                }

                _connection = new SQLiteConnection(connString);
                _connection.Open();
                Logger.LogInformation("数据库连接已建立");

                ExecuteNonQuery(@"PRAGMA journal_mode=WAL;");
                ExecuteNonQuery(@"PRAGMA foreign_keys=ON;");
            }
            catch (Exception ex)
            {
                Logger.LogFatal("数据库初始化失败", ex);
                throw;
            }
        }

        /// <summary>
        /// 创建初始表结构
        /// </summary>
        private void CreateTables()
        {
            var sql = @"
                CREATE TABLE IF NOT EXISTS Bookmarks (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Url TEXT NOT NULL CHECK(length(Url) <= 500),
                    Title TEXT NOT NULL CHECK(length(Title) <= 200),
                    Summary TEXT CHECK(length(Summary) <= 1000),
                    CoverImage TEXT CHECK(length(CoverImage) <= 500),
                    Remark TEXT CHECK(length(Remark) <= 2000),
                    CreateTime DATETIME NOT NULL
                );

                CREATE TABLE IF NOT EXISTS Tags (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL UNIQUE CHECK(length(Name) BETWEEN 2 AND 50),
                    CreateTime DATETIME NOT NULL
                );

                CREATE TABLE IF NOT EXISTS BookmarkTags (
                    BookmarkId INTEGER NOT NULL,
                    TagId INTEGER NOT NULL,
                    PRIMARY KEY (BookmarkId, TagId),
                    FOREIGN KEY (BookmarkId) REFERENCES Bookmarks(Id) ON DELETE CASCADE,
                    FOREIGN KEY (TagId) REFERENCES Tags(Id) ON DELETE CASCADE
                );

                CREATE TABLE IF NOT EXISTS SchemaVersion (
                    Version INTEGER PRIMARY KEY,
                    AppliedOn DATETIME NOT NULL
                );";

            ExecuteNonQuery(sql);
            Logger.LogInformation("数据库表结构已初始化");
        }
        #endregion

        #region 核心操作方法

        /// <summary>
        /// 异步查询方法（支持ORM映射）
        /// </summary>
        public async Task<List<T>> ExecuteQueryAsync<T>(string sql, params SQLiteParameter[] parameters) where T : new()
        {
            return await ExecuteWithRetryAsync(async () =>
            {
                var list = new List<T>();
                using (var cmd = CreateCommand(sql, parameters))
                using (var reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false))
                {
                    var props = typeof(T).GetProperties();

                    while (await reader.ReadAsync().ConfigureAwait(false))
                    {
                        var obj = new T();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            var prop = props.FirstOrDefault(p =>
                                p.Name.Equals(reader.GetName(i), StringComparison.OrdinalIgnoreCase));

                            if (prop != null && !reader.IsDBNull(i))
                            {
                                var value = reader.GetValue(i);
                                prop.SetValue(obj, Convert.ChangeType(value, prop.PropertyType));
                            }
                        }
                        list.Add(obj);
                    }
                }
                return list;
            });
        }

        /// <summary>
        /// 带重试机制的异步执行包装器
        /// </summary>
        private async Task<T> ExecuteWithRetryAsync<T>(Func<Task<T>> action)
        {
            for (int i = 0; i < MaxRetryCount; i++)
            {
                try
                {
                    return await action().ConfigureAwait(false);
                }
                catch (SQLiteException ex) when (IsTransientError(ex))
                {
                    Logger.LogWarning($"数据库操作重试中 ({i + 1}/{MaxRetryCount})");
                    await Task.Delay(100 * (i + 1)).ConfigureAwait(false);
                }
            }
            return await action().ConfigureAwait(false); // 最后一次尝试
        }

        /// <summary>
        /// 执行非查询SQL命令（带重试机制）
        /// </summary>
        public int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
        {
            return ExecuteWithRetry(() =>
            {
                using var cmd = CreateCommand(sql, parameters);
                return cmd.ExecuteNonQuery();
            });
        }

        /// <summary>
        /// 执行查询并返回DataReader
        /// </summary>
        public IEnumerable<IDataRecord> ExecuteReader(string sql, params SQLiteParameter[] parameters)
        {
            using var cmd = CreateCommand(sql, parameters);
            using var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                yield return reader;
            }
        }

        /// <summary>
        /// 执行标量查询
        /// </summary>
        public object ExecuteScalar(string sql, params SQLiteParameter[] parameters)
        {
            return ExecuteWithRetry(() =>
            {
                using var cmd = CreateCommand(sql, parameters);
                return cmd.ExecuteScalar();
            });
        }
        #endregion

        #region 事务管理
        /// <summary>
        /// 开始事务（支持嵌套事务）
        /// </summary>
        public void BeginTransaction()
        {
            _semaphore.Wait();
            try
            {
                if (_transaction == null)
                {
                    _transaction = _connection.BeginTransaction();
                    Logger.LogInformation("事务已开始");
                }
            }
            finally
            {
                _semaphore.Release();
            }
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void Commit()
        {
            _semaphore.Wait();
            try
            {
                _transaction?.Commit();
                _transaction = null;
                Logger.LogInformation("事务已提交");
            }
            finally
            {
                _semaphore.Release();
            }
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void Rollback()
        {
            _semaphore.Wait();
            try
            {
                _transaction?.Rollback();
                _transaction = null;
                Logger.LogWarning("事务已回滚");
            }
            finally
            {
                _semaphore.Release();
            }
        }
        #endregion

        #region 私有方法
        private SQLiteCommand CreateCommand(string sql, SQLiteParameter[] parameters)
        {
            var cmd = _connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandTimeout = DefaultTimeout;
            cmd.Parameters.AddRange(parameters);

            if (_transaction != null)
            {
                cmd.Transaction = _transaction;
            }

            return cmd;
        }

        private T ExecuteWithRetry<T>(Func<T> action)
        {
            for (int i = 0; i < MaxRetryCount; i++)
            {
                try
                {
                    return action();
                }
                catch (SQLiteException ex) when (IsTransientError(ex))
                {
                    Logger.LogWarning($"数据库操作重试中 ({i + 1}/{MaxRetryCount})");
                    Thread.Sleep(100 * (i + 1));
                }
            }
            return action(); // 最后一次尝试不捕获异常
        }

        private bool IsTransientError(SQLiteException ex)
        {
            return ex.ResultCode switch
            {
                SQLiteErrorCode.Busy => true,
                SQLiteErrorCode.Locked => true,
                SQLiteErrorCode.IoErr => true,
                _ => false
            };
        }

        private void MigrateDatabase()
        {
            try
            {
                var version = Convert.ToInt32(ExecuteScalar(
                    "SELECT Version FROM SchemaVersion ORDER BY Version DESC LIMIT 1") ?? 0);

                if (version < 1)
                {
                    CreateTables();
                    ExecuteNonQuery(
                        "INSERT INTO SchemaVersion (Version, AppliedOn) VALUES (@version, @now)",
                        new SQLiteParameter("@version", CurrentVersion),
                        new SQLiteParameter("@now", DateTime.Now));
                }
            }
            catch (SQLiteException ex) when (ex.Message.Contains("no such table"))
            {
                CreateTables();
            }
        }
        #endregion

        #region IDisposable实现
        private bool _disposed = false;

        public void Dispose()
        {
            if (_disposed) return;

            try
            {
                // 先获取信号量保证操作原子性
                _semaphore.Wait();

                // 释放数据库资源
                _transaction?.Dispose();
                _connection?.Close();
                _connection?.Dispose();
            }
            finally
            {
                try
                {
                    // 先释放信号量操作
                    if (_semaphore.CurrentCount == 0)
                        _semaphore.Release();
                }
                finally
                {
                    // 最后释放信号量对象本身
                    _semaphore.Dispose();
                    _disposed = true;
                    GC.SuppressFinalize(this);
                    Logger.LogInformation("数据库连接已释放");
                }
            }
        }


        ~DatabaseHelper() => Dispose();
        #endregion

        #region 1

        /// <summary>
        /// 异步执行标量查询（支持重试机制）
        /// </summary>
        public async Task<object> ExecuteScalarAsync(string sql, params SQLiteParameter[] parameters)
        {
            return await ExecuteWithRetryAsync(async () =>
            {
                using (var cmd = CreateCommand(sql, parameters))
                {
                    return await cmd.ExecuteScalarAsync().ConfigureAwait(false);
                }
            });
        }

        /// <summary>
        /// 异步执行非查询SQL命令（支持重试机制）
        /// </summary>
        public async Task<int> ExecuteNonQueryAsync(string sql, params SQLiteParameter[] parameters)
        {
            return await ExecuteWithRetryAsync(async () =>
            {
                using (var cmd = CreateCommand(sql, parameters))
                {
                    return await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
                }
            });
        }

        /// <summary>
        /// 新增集合参数版本
        /// </summary>
        public async Task<int> ExecuteNonQueryAsync(string sql, IEnumerable<SQLiteParameter> parameters)
        {
            return await ExecuteWithRetryAsync(async () =>
            {
                using (var cmd = CreateCommand(sql, parameters.ToArray()))
                {
                    return await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
                }
            });
        }

        /// <summary>
        /// 安全关闭数据库连接并提交未完成事务
        /// </summary>
        public void CloseAndFlush()
        {
            if (_disposed) return;

            _semaphore.Wait();
            try
            {
                // 提交未完成事务
                if (_transaction?.Connection != null)
                {
                    try
                    {
                        _transaction.Commit();
                        Logger.LogInformation("未提交事务已提交");
                    }
                    catch (Exception ex)
                    {
                        Logger.LogError("事务提交失败", ex);
                        _transaction.Rollback();
                    }
                }

                // 释放数据库连接
                _connection?.Close();
                _connection?.Dispose();
                _connection = null;

                Logger.LogInformation("数据库连接已安全关闭");
            }
            finally
            {
                _semaphore.Release();
                _disposed = true;
                GC.SuppressFinalize(this);
            }
        }

        #endregion 1

        #region 2

        //private readonly SemaphoreSlim _semaphore = new SemaphoreSlim(1, 1);
        //private SQLiteConnection _connection;
        //private bool _disposed;

        //public async Task<List<T>> ExecuteQueryAsync<T>(string query) where T : class, new()
        //{
        //    await _semaphore.WaitAsync();
        //    try
        //    {
        //        if (_connection == null)
        //        {
        //            _connection = new SQLiteConnection(GetConnectionString());
        //            await _connection.OpenAsync();
        //        }
        //        else if (_connection.State != ConnectionState.Open)
        //        {
        //            await _connection.OpenAsync();
        //        }

        //        // 实际查询操作
        //        return await QueryDatabase<T>(query);
        //    }
        //    finally
        //    {
        //        _semaphore.Release();
        //    }
        //}

        //private async Task<List<T>> QueryDatabase<T>(string query) where T : class, new()
        //{
        //    using (var cmd = new SQLiteCommand(query, _connection))
        //    using (var reader = await cmd.ExecuteReaderAsync())
        //    {
        //        var result = new List<T>();
        //        while (await reader.ReadAsync())
        //        {
        //            var item = new T();
        //            foreach (var prop in typeof(T).GetProperties())
        //            {
        //                if (reader[prop.Name] != DBNull.Value)
        //                {
        //                    prop.SetValue(item, reader[prop.Name]);
        //                }
        //            }
        //            result.Add(item);
        //        }
        //        return result;
        //    }
        //}

        //private string GetConnectionString()
        //{
        //    return this.ConnectionString;
        //}

        #endregion 2

    }
}